﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class LotteryCompanyDataAccess : DataBase
    {
        public void Insert(LotteryCompany company)
        {
            string sql = string.Format(@"Insert into {0} ({1},{2},{3},{4},{5},{6})values(@{1},@{2},@{3},@{4},@{5},@{6})  SELECT IDENT_CURRENT('{0}')",
                LotteryCompany.TABLENAME, LotteryCompany.NAME, LotteryCompany.ABBREVIATION, LotteryCompany.TYPEID,
                LotteryCompany.REGION, LotteryCompany.OPENTIME, LotteryCompany.CLOSETIME);
            object id = base.ExecuteScalar(sql, new SqlParameter(LotteryCompany.NAME, company.Name),
                new SqlParameter(LotteryCompany.ABBREVIATION, company.Abbreviation),
                new SqlParameter(LotteryCompany.TYPEID, company.CompanyType.Id),
                new SqlParameter(LotteryCompany.REGION, (int)company.Region),
                new SqlParameter(LotteryCompany.OPENTIME, company.OpenTime),
                new SqlParameter(LotteryCompany.CLOSETIME, company.CloseTime));
            company.CompanyId = Convert.ToInt32(id);
        }
        public void Delete(int companyId)
        {
            string sql = string.Format(@"delete {0} WHERE {1}=@{1}", LotteryCompany.TABLENAME, LotteryCompany.COMPANYID);
            base.ExecuteNonQuery(sql, new SqlParameter(LotteryCompany.COMPANYID, companyId));
        }
        public void Delete(string companyName)
        {
            string sql = string.Format(@"Delete {0} WHERE {1}=@{1}", LotteryCompany.TABLENAME, LotteryCompany.NAME);
            base.ExecuteNonQuery(sql, new SqlParameter(LotteryCompany.NAME, companyName));
        }
        public void Update(LotteryCompany company)
        {
            string sql = string.Format(@"Update {0} SET {1}=@{1},{2}=@{2},{3}=@{3},{4}=@{4},{5}=@{5},{6}=@{6} WHERE {7}=@{7}",
                LotteryCompany.TABLENAME, LotteryCompany.NAME, LotteryCompany.ABBREVIATION, LotteryCompany.TYPEID,
                LotteryCompany.REGION, LotteryCompany.OPENTIME, LotteryCompany.CLOSETIME, LotteryCompany.COMPANYID);
            base.ExecuteNonQuery(sql, new SqlParameter(LotteryCompany.NAME, company.Name),
            new SqlParameter(LotteryCompany.ABBREVIATION, company.Abbreviation),
            new SqlParameter(LotteryCompany.TYPEID, company.CompanyType.Id),
            new SqlParameter(LotteryCompany.REGION, (int)company.Region),
            new SqlParameter(LotteryCompany.OPENTIME, company.OpenTime),
            new SqlParameter(LotteryCompany.CLOSETIME, company.CloseTime),
            new SqlParameter(LotteryCompany.COMPANYID, company.CompanyId));
        }

        /// <summary>
        /// 是否存在公司.
        /// </summary>
        /// <param name="companyName">Name of the company.</param>
        /// <returns></returns>
        public bool ExistCompany(string companyName)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1}", LotteryCompany.TABLENAME, LotteryCompany.NAME);
            object scalar = base.ExecuteScalar(sql, new SqlParameter(LotteryCompany.NAME, companyName));
            int count = Convert.ToInt32(scalar);
            return count > 0;
        }

        public LotteryCompany GetCompany(int companyId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", LotteryCompany.TABLENAME, LotteryCompany.COMPANYID);
            return base.ExecuteEntity<LotteryCompany>(sql, new SqlParameter(LotteryCompany.COMPANYID, companyId));
        }
        public IList<LotteryCompany> GetAllCompany()
        {
            string sql = string.Format(@"SELECT lc.*,ct.Id,ct.TypeName,ct.CreateTime as CompanyType_CreateTime FROM {0} as lc JOIN {1} as ct on ct.{2}=lc.{3}",
                LotteryCompany.TABLENAME, CompanyTypeModel.TABLENAME,
                CompanyTypeModel.ID, LotteryCompany.TYPEID);
            return base.ExecuteList<LotteryCompany>(sql);
        }
    }
}
